using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Marlimer.Common;
using Marlimer.Common.EntidadesQuery;
using Marlimer.Common.Entidades;
using Marlimer.Common.EntidadesGrilla;

namespace Marlimer.Data.Comercial
{
    public class com_Cliente
    {
        public resulOMsgError guardarCliente(InfoCliente cli)
        {
            resulOMsgError resul = new resulOMsgError();
            SqlCommand sqlcmd = new SqlCommand();
            Int32 cnewccod_cli = new Int32();

            //creamos el objeto transaccion
            SqlTransaction transac = Session.getConexion().BeginTransaction("savecli");  

            try
            {
                sqlcmd.CommandText = "spiaCliente";
                sqlcmd.CommandType = CommandType.StoredProcedure;
                sqlcmd.Connection = Session.getConexion();
                sqlcmd.Transaction = transac;
                sqlcmd.Parameters.Add("@ccod_cli", SqlDbType.Int);
                sqlcmd.Parameters.Add("@cape_pat", SqlDbType.NVarChar,25);
                sqlcmd.Parameters.Add("@cape_mat", SqlDbType.NVarChar,25);
                sqlcmd.Parameters.Add("@cnombres", SqlDbType.NVarChar,30);
                sqlcmd.Parameters.Add("@craz_soc", SqlDbType.NVarChar,70);
                sqlcmd.Parameters.Add("@craz_com", SqlDbType.NVarChar,70);
                sqlcmd.Parameters.Add("@ctip_per", SqlDbType.Int);
                sqlcmd.Parameters.Add("@ctip_docide", SqlDbType.Int);
                sqlcmd.Parameters.Add("@cnum_docide", SqlDbType.NVarChar,11);
                sqlcmd.Parameters.Add("@ccod_canal", SqlDbType.Int);
                sqlcmd.Parameters.Add("@ccod_giro", SqlDbType.Int);
                sqlcmd.Parameters.Add("@ccod_modulo", SqlDbType.NVarChar,15);
                sqlcmd.Parameters.Add("@cdir_fiscal", SqlDbType.NVarChar,70);
                sqlcmd.Parameters.Add("@ccod_ubifis", SqlDbType.Int);
                sqlcmd.Parameters.Add("@cref_fis", SqlDbType.NVarChar,70);
                sqlcmd.Parameters.Add("@nsecuencia", SqlDbType.Decimal);
                sqlcmd.Parameters.Add("@nvisita", SqlDbType.Int);
                sqlcmd.Parameters.Add("@cpagweb", SqlDbType.NVarChar,70);
                sqlcmd.Parameters.Add("@cemail", SqlDbType.NVarChar,50);
                sqlcmd.Parameters.Add("@cobservacion", SqlDbType.NVarChar, 255);  
                sqlcmd.Parameters.Add("@ncoordx", SqlDbType.Float);
                sqlcmd.Parameters.Add("@ncoordy", SqlDbType.Float);
                sqlcmd.Parameters.Add("@cpc_crea", SqlDbType.NVarChar,50);
                sqlcmd.Parameters.Add("@cuser_crea", SqlDbType.Int);
                sqlcmd.Parameters.Add("@cmodo", SqlDbType.Int);

                sqlcmd.Parameters["@ccod_cli"].Value = cli.ccod_cli;
                sqlcmd.Parameters["@cape_pat"].Value = cli.cape_pat;
                sqlcmd.Parameters["@cape_mat"].Value = cli.cape_mat;
                sqlcmd.Parameters["@cnombres"].Value = cli.cnombres;
                sqlcmd.Parameters["@craz_soc"].Value = cli.craz_soc;
                sqlcmd.Parameters["@craz_com"].Value = cli.craz_com;
                sqlcmd.Parameters["@ctip_per"].Value = cli.ctip_per;
                sqlcmd.Parameters["@ctip_docide"].Value = cli.ctip_docide;
                sqlcmd.Parameters["@cnum_docide"].Value = cli.cnum_docide;
                sqlcmd.Parameters["@ccod_canal"].Value = cli.ccod_canal;
                sqlcmd.Parameters["@ccod_giro"].Value = cli.ccod_giro;
                sqlcmd.Parameters["@ccod_modulo"].Value = cli.ccod_modulo;
                sqlcmd.Parameters["@cdir_fiscal"].Value = cli.cdir_fiscal;
                sqlcmd.Parameters["@ccod_ubifis"].Value = cli.ccod_ubifis;
                sqlcmd.Parameters["@cref_fis"].Value = cli.cref_fis;
                sqlcmd.Parameters["@nsecuencia"].Value = cli.nsecuencia;
                sqlcmd.Parameters["@nvisita"].Value = cli.nvisita;
                sqlcmd.Parameters["@cpagweb"].Value = cli.cpagweb;
                sqlcmd.Parameters["@cemail"].Value = cli.cemail;
                sqlcmd.Parameters["@cobservacion"].Value = cli.cobservacion;
                if (cli.ncoordy != 0)
                {
                    sqlcmd.Parameters["@ncoordx"].Value = DBNull.Value;
                    sqlcmd.Parameters["@ncoordy"].Value = DBNull.Value;
                }
                else
                {
                    sqlcmd.Parameters["@ncoordx"].Value = cli.ncoordx;
                    sqlcmd.Parameters["@ncoordy"].Value = cli.ncoordy;
                }
                sqlcmd.Parameters["@ncoordx"].Value =  DBNull.Value;
                sqlcmd.Parameters["@ncoordy"].Value = DBNull.Value;
                sqlcmd.Parameters["@cpc_crea"].Value = cli.cpc_crea;
                sqlcmd.Parameters["@cuser_crea"].Value = cli.cuser_crea;
                sqlcmd.Parameters["@cmodo"].Value = cli.cmodo;
                long ccod_cli = 0;
                if (cli.cmodo == 1)
                {//insertar
                    ccod_cli = Convert.ToInt32(sqlcmd.ExecuteScalar());
                }
                else
                {//actualizar
                    ccod_cli = cli.ccod_cli;
                    sqlcmd.ExecuteNonQuery();
                }

                sqlcmd.CommandText = "spiaClienteEntrega";
                sqlcmd.Transaction = transac;
                sqlcmd.Parameters.Clear();
                sqlcmd.Parameters.Add("@ccod_dirent", SqlDbType.Int);
                sqlcmd.Parameters.Add("@ccod_cli", SqlDbType.Int);
                sqlcmd.Parameters.Add("@cdir_ent", SqlDbType.NVarChar,70);
                sqlcmd.Parameters.Add("@ccod_ubient", SqlDbType.Int);
                sqlcmd.Parameters.Add("@cref_ent", SqlDbType.NVarChar,70);
                sqlcmd.Parameters.Add("@cstatus", SqlDbType.NChar,1);
                sqlcmd.Parameters.Add("@cpc_crea", SqlDbType.NVarChar,50);
                sqlcmd.Parameters.Add("@cuser_crea", SqlDbType.Int);
                sqlcmd.Parameters.Add("@cmodo", SqlDbType.Int);

                //recorrer las direcciones y telefonos
                foreach (InfoClienteEntrega client in cli.dirents)
                {
                    sqlcmd.Parameters["@ccod_dirent"].Value = client.ccod_dirent;
                    sqlcmd.Parameters["@ccod_cli"].Value = ccod_cli;
                    sqlcmd.Parameters["@cdir_ent"].Value = client.cdir_ent;
                    sqlcmd.Parameters["@ccod_ubient"].Value = client.ccod_ubient;
                    sqlcmd.Parameters["@cref_ent"].Value = client.cref_ent;
                    sqlcmd.Parameters["@cstatus"].Value = client.cstatus;
                    sqlcmd.Parameters["@cpc_crea"].Value = client.cpc_crea;  
                    sqlcmd.Parameters["@cuser_crea"].Value = client.cuser_crea;
                    if (client.ccod_dirent > 0)
                    {
                        sqlcmd.Parameters["@cmodo"].Value = 2;
                    }
                    else
                    {
                        sqlcmd.Parameters["@cmodo"].Value = 1;
                    }
                    sqlcmd.ExecuteNonQuery(); 
                }

                sqlcmd.CommandText = "spiaClienteTelefono";
                sqlcmd.Transaction = transac;
                sqlcmd.Parameters.Clear();
                sqlcmd.Parameters.Add("@ccod_telf", SqlDbType.Int);
                sqlcmd.Parameters.Add("@ccod_cli", SqlDbType.Int);
                sqlcmd.Parameters.Add("@ctip_telf", SqlDbType.Int);
                sqlcmd.Parameters.Add("@cnum_telf", SqlDbType.NVarChar,15);
                sqlcmd.Parameters.Add("@cnum_anex", SqlDbType.NVarChar,15);
                sqlcmd.Parameters.Add("@cstatus", SqlDbType.NChar,1);
                sqlcmd.Parameters.Add("@cmodo", SqlDbType.Int);

                foreach (InfoClienteTelefono clitelf in cli.telfs)
                {
                    sqlcmd.Parameters["@ccod_telf"].Value = clitelf.ccod_telf;
                    sqlcmd.Parameters["@ccod_cli"].Value = ccod_cli;
                    sqlcmd.Parameters["@ctip_telf"].Value = clitelf.ctip_telf;
                    sqlcmd.Parameters["@cnum_telf"].Value = clitelf.cnum_telf;
                    sqlcmd.Parameters["@cnum_anex"].Value = clitelf.cnum_anex;
                    sqlcmd.Parameters["@cstatus"].Value = clitelf.cstatus;
                    //si tiene codigo mayor que 0 entonces es modo 2
                    if (clitelf.ccod_telf > 0)
                    {
                        sqlcmd.Parameters["@cmodo"].Value = 2;
                    }
                    else
                    {
                        sqlcmd.Parameters["@cmodo"].Value = 1;
                    }
                    sqlcmd.ExecuteNonQuery();
                }
                transac.Commit(); 
                resul.message = "Se guardo el cliente con codigo " + ccod_cli.ToString()  + " satisfactoriamente.";
                cnewccod_cli = (Int32)ccod_cli;
                resul.clase = cnewccod_cli;
                resul.resul = true;
            }
            catch (Exception ex)
            {
                transac.Rollback(); 
                resul.resul = false;
                resul.message = ex.Message;
            }
            return resul;
        }

        public resulMsgError actualizaModuloCliente(List<InfoClienteActMasivo> clies)
        {
            resulMsgError resul = new resulMsgError(false, "");
            SqlCommand sqlcmd = new SqlCommand();

            //creamos el objeto transaccion
            SqlTransaction transac = Session.getConexion().BeginTransaction("masiclies");

            try
            {
                sqlcmd.CommandText = "spuModuloCliente";
                sqlcmd.CommandType = CommandType.StoredProcedure;
                sqlcmd.Connection = Session.getConexion();
                sqlcmd.Transaction = transac;
                sqlcmd.Parameters.Add("@ccod_cli", SqlDbType.Int);
                sqlcmd.Parameters.Add("@ccod_modulo", SqlDbType.NVarChar, 15);
                sqlcmd.Parameters.Add("@cpc_crea", SqlDbType.NVarChar, 50);
                sqlcmd.Parameters.Add("@cuser_crea", SqlDbType.Int);

                //valores inamovibles
                sqlcmd.Parameters["@cpc_crea"].Value = Session.getComputerName();
                sqlcmd.Parameters["@cuser_crea"].Value = Session.getInfousuario().ccod_user;    

                foreach (InfoClienteActMasivo cli in clies)
                {
                    sqlcmd.Parameters["@ccod_cli"].Value = cli.ccod_cli;
                    sqlcmd.Parameters["@ccod_modulo"].Value = cli.ccod_modulo;
                    sqlcmd.ExecuteNonQuery(); 
                }
                transac.Commit();
                resul.message = "Se guardaron los clientes satisfactoriamente"; 
                resul.resul = true;
            }
            catch (Exception ex)
            {
                transac.Rollback();
                resul.resul = false;
                resul.message = ex.Message;
            }
            return resul;
        }

        public resulMsgError actualizaSecuenciaCliente(List<InfoClienteActMasivo> clies)
        {
            resulMsgError resul = new resulMsgError(false, "");
            SqlCommand sqlcmd = new SqlCommand();  

            //creamos el objeto transaccion
            SqlTransaction transac = Session.getConexion().BeginTransaction("masiclies");

            try
            {
                sqlcmd.CommandText = "spuSecuenciaCliente";
                sqlcmd.CommandType = CommandType.StoredProcedure;
                sqlcmd.Connection = Session.getConexion();
                sqlcmd.Transaction = transac;
                sqlcmd.Parameters.Add("@ccod_cli", SqlDbType.Int);
                sqlcmd.Parameters.Add("@nsecuencia", SqlDbType.Decimal);
                sqlcmd.Parameters.Add("@cpc_crea", SqlDbType.NVarChar, 50);
                sqlcmd.Parameters.Add("@cuser_crea", SqlDbType.Int);

                //valores inamovibles
                sqlcmd.Parameters["@cpc_crea"].Value = Session.getComputerName();
                sqlcmd.Parameters["@cuser_crea"].Value = Session.getInfousuario().ccod_user;

                foreach (InfoClienteActMasivo cli in clies)
                {
                    sqlcmd.Parameters["@ccod_cli"].Value = cli.ccod_cli;
                    sqlcmd.Parameters["@nsecuencia"].Value = cli.nsecuencia;
                    sqlcmd.ExecuteNonQuery();
                }
                transac.Commit();
                resul.message = "Se guardaron los clientes satisfactoriamente";
                resul.resul = true;
            }
            catch (Exception ex)
            {
                transac.Rollback();
                resul.resul = false;
                resul.message = ex.Message;
            }
            return resul;
        }



        public DataTable getBuscarCliente(int ccod_cli, int ctip_docide, String cnum_docide,
                    String craz_soc, String cdir_ent,String ccod_modulo, int ccod_ruta,
                    int ccod_giro, int ccod_ubigeo, int ndia_visita, String cstatus)
        {
            SqlCommand sqlcmd = new SqlCommand();
            SqlDataAdapter da = new SqlDataAdapter();
            DataSet ds = new DataSet();

            sqlcmd.CommandText = "spsBuscarCliente";
            sqlcmd.Connection = Session.getConexion(); 
            sqlcmd.CommandType = CommandType.StoredProcedure;  

            sqlcmd.Parameters.Add("@ccod_cli", SqlDbType.Int);
            sqlcmd.Parameters.Add("@ctip_docide", SqlDbType.Int);
            sqlcmd.Parameters.Add("@cnum_docide", SqlDbType.NVarChar,11);
            sqlcmd.Parameters.Add("@craz_soc", SqlDbType.NVarChar,70);
            sqlcmd.Parameters.Add("@cdir_ent", SqlDbType.NVarChar,70);
            sqlcmd.Parameters.Add("@ccod_modulo", SqlDbType.NVarChar,15);
            sqlcmd.Parameters.Add("@ccod_ruta", SqlDbType.Int);
            sqlcmd.Parameters.Add("@ccod_giro", SqlDbType.Int);
            sqlcmd.Parameters.Add("@ccod_ubigeo", SqlDbType.Int);
            sqlcmd.Parameters.Add("@ndia_visita", SqlDbType.Int);
            sqlcmd.Parameters.Add("@cstatus", SqlDbType.NChar,1 );

            sqlcmd.Parameters["@ccod_cli"].Value= ccod_cli;
            sqlcmd.Parameters["@ctip_docide"].Value= ctip_docide;
            sqlcmd.Parameters["@cnum_docide"].Value= cnum_docide;
            sqlcmd.Parameters["@craz_soc"].Value= craz_soc;
            sqlcmd.Parameters["@cdir_ent"].Value= cdir_ent;
            sqlcmd.Parameters["@ccod_modulo"].Value= ccod_modulo;
            sqlcmd.Parameters["@ccod_ruta"].Value= ccod_ruta;
            sqlcmd.Parameters["@ccod_giro"].Value= ccod_giro;
            sqlcmd.Parameters["@ccod_ubigeo"].Value= ccod_ubigeo;
            sqlcmd.Parameters["@ndia_visita"].Value= ndia_visita;
            sqlcmd.Parameters["@cstatus"].Value= cstatus;

            da.SelectCommand = sqlcmd;
            da.Fill (ds);
            return ds.Tables[0];
        }

        public DataTable getClientesRechazosConsecutivos(DateTime dfec_ini, DateTime dfec_fin, int ccod_zona, int ccod_ruta, string ccod_modulo, int flg_anulados)
        {
            SqlCommand sqlcmd = new SqlCommand();
            SqlDataAdapter da = new SqlDataAdapter();
            DataSet ds = new DataSet();

            sqlcmd.CommandText = "spsConsultaClientesDevConsecutivos";
            sqlcmd.Connection = Session.getConexion();
            sqlcmd.CommandType = CommandType.StoredProcedure;

            sqlcmd.Parameters.Add("@dfec_ini", SqlDbType.DateTime);
            sqlcmd.Parameters.Add("@dfec_fin", SqlDbType.DateTime);
            sqlcmd.Parameters.Add("@ccod_zona", SqlDbType.Int);
            sqlcmd.Parameters.Add("@ccod_ruta", SqlDbType.Int);
            sqlcmd.Parameters.Add("@ccod_modulo", SqlDbType.NVarChar, 15);
            sqlcmd.Parameters.Add("@flg_anulados", SqlDbType.Int);
            
            sqlcmd.Parameters["@dfec_ini"].Value = dfec_ini;
            sqlcmd.Parameters["@dfec_fin"].Value = dfec_fin;
            sqlcmd.Parameters["@ccod_zona"].Value = ccod_zona;
            sqlcmd.Parameters["@ccod_ruta"].Value = ccod_ruta;
            sqlcmd.Parameters["@ccod_modulo"].Value = ccod_modulo;
            sqlcmd.Parameters["@flg_anulados"].Value = flg_anulados;

            da.SelectCommand = sqlcmd;
            da.Fill(ds);
            return ds.Tables[0];
        }

        //procedimiento para anular un cliente cambiando su estado
        public resulMsgError anularCliente(int ccod_cli, int ccod_motbaja, int flg_acti)
        {
            resulMsgError resulO = new resulMsgError(false, "");
            SqlCommand sqlcmd = new SqlCommand();
            sqlcmd.Connection = Session.getConexion();
            sqlcmd.CommandText = "spuAnularCliente";
            sqlcmd.CommandType = CommandType.StoredProcedure;
            sqlcmd.Parameters.Add("@ccod_cli", SqlDbType.Int);
            sqlcmd.Parameters.Add("@ccod_motbaja", SqlDbType.Int);
            sqlcmd.Parameters.Add("@cpc_modi", SqlDbType.NVarChar,50);
            sqlcmd.Parameters.Add("@cuser_modi", SqlDbType.Int);
            sqlcmd.Parameters.Add("@flg_acti", SqlDbType.Int);

            sqlcmd.Parameters["@ccod_cli"].Value = ccod_cli;
            sqlcmd.Parameters["@ccod_motbaja"].Value = ccod_motbaja;
            sqlcmd.Parameters["@cpc_modi"].Value = Session.getComputerName();
            sqlcmd.Parameters["@cuser_modi"].Value = Session.getInfousuario().ccod_user;
            sqlcmd.Parameters["@flg_acti"].Value = flg_acti;

            try
            {
                sqlcmd.ExecuteNonQuery();
                resulO.message = "Se anulo el cliente satisfactoriamente.";
                resulO.resul = true; 
            }
            catch (Exception ex)
            {
                resulO.message = ex.Message;
            }
            return resulO; 
        }

        //Procedimiento que permite retornar informacion de cliente
        //buscado por codigo
        public resulOMsgError getClienteByCod(int ccod_cli)
        {

            resulOMsgError resulO = new resulOMsgError(true, "", null);
            InfoClienteBus infoClienteBus = new InfoClienteBus();
            SqlCommand sqlcmd = new SqlCommand();
            SqlDataAdapter da = new SqlDataAdapter();
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();

            try
            {
                sqlcmd.Connection = Session.getConexion();
                sqlcmd.CommandText = "spsBuscarClienteByCod";
                sqlcmd.CommandType = CommandType.StoredProcedure;
                sqlcmd.Parameters.Add("@ccod_cli", SqlDbType.Int);

                sqlcmd.Parameters["@ccod_cli"].Value = ccod_cli;
                da.SelectCommand = sqlcmd;
                da.Fill(ds);
                dt = ds.Tables[0];
            }
            catch (Exception ex)
            {
                resulO.message = ex.Message;
                resulO.resul = false;
            }
            finally
            {
                if (dt != null)
                {
                    if (dt.Rows.Count > 0)
                    {
                        object[] items = dt.Rows[0].ItemArray;

                        for (int i = 0; i < items.Length; i++)
                        {
                            //infoClienteBus.getElementos()[i] = items.GetValue(i);
                            infoClienteBus.setElement(i, items.GetValue(i));  
                        }

                        resulO.resul = true;
                        resulO.clase = infoClienteBus;
                    }
                    else
                    {
                        resulO.resul = false;
                        resulO.message = "El codigo de cliente " + ccod_cli + " no existe. Intente con otro";
                    }
                }
            }
            return resulO;
        }

        public DataTable getClienteDatosByCod(int ccod_cli)
        {
            SqlCommand sqlcmd = new SqlCommand();
            SqlDataAdapter da = new SqlDataAdapter();
            DataSet ds = new DataSet();

            sqlcmd.CommandText = "spsClienteDatosByCod";
            sqlcmd.CommandType = CommandType.StoredProcedure;
            sqlcmd.Connection = Session.getConexion();
            sqlcmd.Parameters.Add("@ccod_cli", SqlDbType.Int);
            sqlcmd.Parameters["@ccod_cli"].Value = ccod_cli;

            da.SelectCommand = sqlcmd;
            da.Fill(ds);
            return ds.Tables[0]; 
        }

        public DataTable getClienteEntregaByCod(int ccod_cli)
        {
            SqlCommand sqlcmd = new SqlCommand();
            SqlDataAdapter da = new SqlDataAdapter();
            DataSet ds = new DataSet(); 

            sqlcmd.CommandText = "spsClienteEntregaByCod";
            sqlcmd.CommandType = CommandType.StoredProcedure; 
            sqlcmd.Connection = Session.getConexion();
            sqlcmd.Parameters.Add("ccod_cli", SqlDbType.Int);
            sqlcmd.Parameters["ccod_cli"].Value = ccod_cli;

            da.SelectCommand = sqlcmd;
            da.Fill(ds);
            return ds.Tables[0]; 
        }

        public DataTable getClienteTelefByCod(int ccod_cli)
        {
            SqlCommand sqlcmd = new SqlCommand();
            SqlDataAdapter da = new SqlDataAdapter();
            DataSet ds = new DataSet();

            sqlcmd.CommandText = "spsClienteTelefonoByCod";
            sqlcmd.CommandType = CommandType.StoredProcedure;
            sqlcmd.Connection = Session.getConexion();
            sqlcmd.Parameters.Add("@ccod_cli", SqlDbType.Int);
            sqlcmd.Parameters["@ccod_cli"].Value = ccod_cli;

            da.SelectCommand = sqlcmd;
            da.Fill(ds);
            return ds.Tables[0]; 
        }



        public static DataTable getListaClienteSecuencia(String ccod_modulo)
        {
            SqlCommand sqlcmd = new SqlCommand();
            SqlDataAdapter da = new SqlDataAdapter();
            DataSet ds = new DataSet();

            sqlcmd.CommandText = "spsClienteBySecuencia";
            sqlcmd.CommandType = CommandType.StoredProcedure;
            sqlcmd.Connection = Session.getConexion();
            sqlcmd.Parameters.Add("@ccod_modulo", SqlDbType.NVarChar, 15);
            sqlcmd.Parameters["@ccod_modulo"].Value = ccod_modulo;

            da.SelectCommand = sqlcmd;
            da.Fill(ds);
            return ds.Tables[0]; 
            
        }
    }
}
